package com.lijin.workshop.event.ccbcard;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.lijin.workshop.util.FileLineHandler;
import com.lijin.workshop.util.FileUtil;

public class CCBCardProcess {
	
	private static String preCard = null;
	private static int singleCnt = 0;
	private static int multiCnt = 0;

	public static void main(String[] args) throws IOException {
		mergeCCBCard();
	}

	private static void mergeCCBCard() throws IOException {
		String cFile = "D:\\ccb_card_c.del";
		String resultFile1 = "D:\\single_card.txt";
		String resultFile2 = "D:\\multi_card.txt";
		String resultFile3 = "D:\\ccb_card_result.txt";
		final BufferedWriter writer1 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile1), "GBK"));
		final BufferedWriter writer2 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile2), "GBK"));
		final BufferedWriter writer3 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile3), "GBK"));
		FileUtil.readFileByLine(cFile, "GBK", new FileLineHandler() {
			@Override
			public void handleLine(String line) {
				if (line == null || line.trim().length() == 0) {
					System.out.println("空行跳过");
					return;
				}
				String[] strs = line.split("[,]");
				if (strs.length != 6) {
					System.out.println("格式不符合:" + line);
					return;
				}
				String currCard = strs[0];
				try {
					if (currCard.equals(preCard)) {
						writer2.write(line);
						writer2.newLine();
						multiCnt ++;
					} else {
						writer1.write(line);
						writer1.newLine();
						
						String card = strs[0].replace("\"", "").trim();
						String phone = strs[1].replace("\"", "").trim();
						String tm = strs[2].replace("\"", "").trim();
						
						writer3.write(StringUtils.rightPad(card, 19) + StringUtils.rightPad(phone, 22) + tm);
						writer3.newLine();
						
						preCard = strs[0];
						singleCnt ++;
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		});
		writer1.close();
		writer2.close();
		writer3.close();
		System.out.println("multiCnt -->" + multiCnt);
		System.out.println("singleCnt -->" + singleCnt);
	}
	
	private static void diffCardFile() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/test";
		String user = "root";
		String password = "111111";
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, user, password);
			if (!conn.isClosed()) {
				System.out.println("Succeeded connecting to the Database!");
			}
			Statement statement = conn.createStatement();
			String sql = "select distinct DATE from ccb_card order by DATE DESC";
			// 结果集
			ResultSet rs = statement.executeQuery(sql);
			System.out.println("-----------------");
			System.out.println("执行结果如下所示:");
			System.out.println("-----------------");
			List<String> dtLst = new ArrayList<String>();
			while (rs.next()) {
				dtLst.add(rs.getString("DATE"));
			}
			rs.close();
			statement.close();
			
			for (String dt : dtLst) {
				System.out.println("-------处理日期:" + dt + "开始--------");
				if (dt.equals("20140313")
						|| dt.equals("20140312")
						|| dt.equals("20140311")
						|| dt.equals("20140310")) {
					System.out.println(dt + "已经抽取");
					continue;
				}
				String sql_1 = "select * from ccb_card where date='" + dt + "'";
				Statement st2 = conn.createStatement();
				ResultSet rs2 = st2.executeQuery(sql_1);
				Map<String, Map<String, String>> cardMap = new HashMap<String, Map<String,String>>();
				while(rs2.next()) {
					String cardNo = rs2.getString("CARD_NO");
					String phone = rs2.getString("PHONE");
					String bin = rs2.getString("BIN");
					int cardLen = rs2.getInt("CARD_LEN");
					String cardAttr = rs2.getString("CARD_ATTR");
					if (cardMap.get(cardNo) == null) {
						Map<String, String> m = new HashMap<String, String>();
						m.put("PHONE", phone);
						m.put("BIN", bin);
						m.put("CARD_LEN", String.valueOf(cardLen));
						m.put("CARD_ATTR", cardAttr);
						cardMap.put(cardNo, m);
					}
				}
				rs2.close();
				st2.close();
				System.out.println(dt + "--" + cardMap.size());
				
				String saveSql = "insert into ccb_card_new (CARD_NO, PHONE, DATE, BIN, CARD_LEN, CARD_ATTR) values (?,?,?,?,?,?)";
				PreparedStatement st4 = conn.prepareStatement(saveSql);
				for (String cardNo : cardMap.keySet()) {
					String sql3 = "select count(*) as CNT from ccb_card_new where card_no='" + cardNo + "'";
					Statement st3 = conn.createStatement();
					ResultSet rs3 = st3.executeQuery(sql3);
					int cnt = 0;
					if (rs3.next()) {
						cnt = rs3.getInt("CNT");
					}
					rs3.close();
					st3.close();
					
					if (cnt > 0) {
						System.out.println("卡片已存在:" + cardNo);
						continue;
					}
					
					Map<String, String> m = cardMap.get(cardNo);
					String phone = m.get("PHONE");
					String bin = m.get("BIN");
					int cardLen = Integer.parseInt(m.get("CARD_LEN"));
					String cardAttr = m.get("CARD_ATTR");
					st4.setString(1, cardNo);
					st4.setString(2, phone);
					st4.setString(3, dt);
					st4.setString(4, bin);
					st4.setInt(5, cardLen);
					st4.setString(6, cardAttr);
					st4.executeUpdate();
				}
				st4.close();
				System.out.println("-------处理日期:" + dt + "完成--------");
			}
			
			
//			String sql_1 = "select * fromccb_card where DATE=?";
//			String sql_2 = "select count(*) as CNT from ccb_card_new where card_no=?";
//			String saveSql = "insert into ccb_card_new (CARD_NO, PHONE, DATE, BIN, CARD_LEN, CARD_ATTR) values (?,?,?,?,?,?)";
//			
//			for (String dt : dtLst) {
//				PreparedStatement st = conn.prepareStatement(sql_1);
//				st.setString(1, dt);
//				ResultSet rs1 = st.executeQuery();
//				while (rs1.next()) {
//					String cardNo = rs1.getString("CARD_NO");
//					int cnt = 0;
//					PreparedStatement st2 = conn.prepareStatement(sql_2);
//					st2.setString(1, cardNo);
//					ResultSet rs2 = st2.executeQuery();
//					if (rs2.next()) {
//						cnt = rs2.getInt("CNT");
//					}
//					rs2.close();
//					st2.close();
//					if (cnt > 0) {
//						System.out.println("卡片已存在:" + cardNo);
//						continue;
//					}
//					String phone = rs1.getString("PHONE");
//					String bin = rs1.getString("BIN");
//					int cardLen = rs1.getInt("CARD_LEN");
//					String cardAttr = rs1.getString("CARD_ATTR");
//					
//					PreparedStatement st3 = conn.prepareStatement(saveSql);
//					st3.setString(1, cardNo);
//					st3.setString(2, phone);
//					st3.setString(3, dt);
//					st3.setString(4, bin);
//					st3.setInt(5, cardLen);
//					st3.setString(6, cardAttr);
//					st3.executeUpdate();
//					st3.close();
//				}
//				rs1.close();
//				st.close();
//			}
			conn.close();
		} catch (ClassNotFoundException e) {
			System.out.println("Sorry,can`t find the Driver!");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static Map<String, CCBBin> initBinMap() {
		String binFile = "D:\\ccb\\bin.txt";
		final Map<String, CCBBin> binMap = new HashMap<String, CCBBin>();
		FileUtil.readFileByLine(binFile, "GBK", new FileLineHandler() {
			@Override
			public void handleLine(String line) {
				if (line == null || line.trim().length() == 0) {
					System.out.println("空行跳过");
					return;
				}
				String[] strs = line.split("[|]");
				if (strs.length != 4) {
					return;
				}
				CCBBin bin = new CCBBin();
				bin.setCardLen(Integer.parseInt(strs[0].trim()));
				bin.setBinLen(Integer.parseInt(strs[1].trim()));
				bin.setBin(strs[2].trim());
				bin.setCardAttr(strs[3].trim());
				binMap.put(String.valueOf(bin.getCardLen()) + bin.getBin(), bin);
			}
		});
		return binMap;
	}

	private static void filterBiCard(final Map<String, CCBBin> binMap) throws IOException {
		String cardFile = "D:\\ccb\\bi.txt";
		String resultFile1 = "D:\\ccb\\bi_result_1.txt";
		String resultFile2 = "D:\\ccb\\bi_result_2.txt";
		final BufferedWriter writer1 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile1), "GBK"));
		final BufferedWriter writer2 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile2), "GBK"));
		FileUtil.readFileByLine(cardFile, "GBK", new FileLineHandler() {
			@Override
			public void handleLine(String line) {
				if (line.trim().length() >= 80) {
					String recSt = line.substring(104, 106).trim();
					if (recSt.equals("1")) {
						String cardNo = line.substring(0, 20).trim();
						CCBBin bin = findBinFromCache(cardNo, binMap);
						if (bin != null) {
							String phone = line.substring(20, 40).trim();
							String tm = line.substring(40, 51).trim().replace("-", "");
							String rsl1 = StringUtils.rightPad(cardNo, 19)
									+ StringUtils.rightPad(phone, 22) + tm;
							String rsl2 = cardNo + "|" + phone + "|" + tm + "|" + bin.getBin()
									+ "|" + bin.getCardLen() + "|" + bin.getCardAttr();
							try {
								writer1.write(rsl1);
								writer1.newLine();

								writer2.write(rsl2);
								writer2.newLine();
							} catch (IOException e) {
								e.printStackTrace();
							}
						}
					}
				} else {
					System.out.println(line);
				}
			}
		});
		writer1.close();
		writer2.close();
	}

	private static void filterCiCard(final Map<String, CCBBin> binMap) throws IOException {
		String cardFile = "D:\\ccb\\ci.txt";
		String resultFile1 = "D:\\ccb\\result_1.txt";
		String resultFile2 = "D:\\ccb\\result_2.txt";
		final BufferedWriter writer1 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile1), "GBK"));
		final BufferedWriter writer2 = new BufferedWriter(new OutputStreamWriter(
				new FileOutputStream(resultFile2), "GBK"));
		FileUtil.readFileByLine(cardFile, "GBK", new FileLineHandler() {
			@Override
			public void handleLine(String line) {
				if (line.trim().length() >= 80) {
					String cardNo = line.substring(0, 20).trim();
					CCBBin bin = findBinFromCache(cardNo, binMap);
					if (bin != null) {
						String phone = line.substring(20, 40).trim();
						String tm = line.substring(40, 51).trim().replace("-", "");
						String rsl1 = StringUtils.rightPad(cardNo, 19)
								+ StringUtils.rightPad(phone, 22) + tm;
						String rsl2 = cardNo + "|" + phone + "|" + tm + "|" + bin.getBin() + "|"
								+ bin.getCardLen() + "|" + bin.getCardAttr();
						try {
							writer1.write(rsl1);
							writer1.newLine();

							writer2.write(rsl2);
							writer2.newLine();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
				} else {
					System.out.println(line);
				}
			}
		});
		writer1.close();
		writer2.close();
	}

	private static CCBBin findBinFromCache(String card, Map<String, CCBBin> binMap) {
		String bin = null;
		String fullCard = String.valueOf(card.trim().length()) + card;
		int idx = fullCard.length();
		while (idx >= 3) {
			bin = fullCard.substring(0, idx);
			idx--;
			CCBBin b = binMap.get(bin);
			if (b != null) {
				return b;
			}
		}
		return null;
	}
}

class CCBBin {

	private int cardLen;
	private int binLen;
	private String bin;
	private String cardAttr;

	public int getCardLen() {
		return cardLen;
	}
	public void setCardLen(int cardLen) {
		this.cardLen = cardLen;
	}
	public int getBinLen() {
		return binLen;
	}
	public void setBinLen(int binLen) {
		this.binLen = binLen;
	}
	public String getBin() {
		return bin;
	}
	public void setBin(String bin) {
		this.bin = bin;
	}
	public String getCardAttr() {
		return cardAttr;
	}
	public void setCardAttr(String cardAttr) {
		this.cardAttr = cardAttr;
	}

}